一、实验目的表或视图数据的各种查询(与统计)SQL命令操作，具体分为：①了解查询的概念和方法；②掌握SQL Server集成管理器查询子窗口中执行SELECT操作的方法；③掌握SELECT语句在单表查询中的应用；④掌握SELECT语句在多表查询中的应用；⑤掌握SELECT语句在复杂查询中的使用方法。二、实验内容(1)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询:①检索年龄大于23岁的男学生的学号和姓名;②检索至少选修一门课程的女学生姓名;③检索王同学不学的课程的课程号;④检索至少选修两门课程的学生学号;⑤检索全部学生都选修的课程的课程号与课程名;⑥检索选修了所有3学分课程的学生学号。(2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询:①统计所有学生选修的课程门数;②求选修4号课程的学生的平均年龄;③求学分为3的每门课程的学生平均成绩;④统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列;⑤检索学号比王非同学大,而年龄比他小的学生姓名;⑥检索姓名以王打头的所有学生的姓名和年龄;⑦在SC中检索成绩为空值的学生学号和课程号;⑧求年龄大于女同学平均年龄的男学生的姓名和年龄;⑨求年龄大于所有女同学年龄的男学生的姓名和年龄;⑩检索所有比“王华”年龄大的学生姓名、年龄和性别;⑪检索选修2号课程的学生中成绩最高的学生的学号;⑫检索学生姓名及其所选修课程的课程号和成绩;⑬检索选修4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。

(3)设有如下4个基本表(表结构与表内容是假设的)，如表4-1、表4-2、表4-3、表4-4所示，请先创建数据库及根据表内容创建表结构，并添加表记录，实践以下各题的SQL命令操作：①查询选修课程“8105”且成绩在80到90之间的所有记录;②查询成绩为79,89或99的记录;③查询9803班的学生人数;④查询至少有20名学生选修的并以8开头的课程的平均成绩:⑤查询最低分大于80,最高分小于95的SNO与平均分:⑥查询9803班学生所选各课程的课程号及其平均成绩:⑦查询选修“8105”课程的成绩高于“9809”号同学成绩的所有同学的记录:⑧查询与学号为“9808”的同学同岁的所有学生的SNO、SNAME和AGE:⑨查询“钱军”教师任课的课程号,选修其课程学生的学号和成绩;⑩查询选修某课程的学生人数多于20人的教师姓名;⑪查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245"课程的同学的SNO及“8105”课程成绩，并按成绩从高到低次序排列;⑫查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE;⑬列出所有教师和同学的姓名、SEX、AGE;⑭查询成绩比该课程平均成绩高的学生的成绩表;⑮列出所有任课教师的TNAME和DEPT;⑯列出所有未讲课教师的TNAME和DEPT;⑰列出至少有4名男生的班号;⑱查询不姓“张"的学生的记录;⑲查询每门课最高分的学生的SNO、CNO、GRADE;⑳查询与“李华”同性别并同班的同学的SNAME;㉑查询“女”教师及其所上的课程;㉒查询选修“数据库系统”课程的“男”同学的成绩表;㉓查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄;㉔查询不讲授“8104”号课程的教师姓名。三、实验结果--1.1检索年龄大于23岁的男学生的学号和姓名;

select SNO,SNAMEfrom STUDENTwhere SAGE>23 AND SSEX = '男';--1.2检索至少选修一门课程的女学生姓名;

select SNAMEfrom STUDENT,SCwhere STUDENT.SNO=SC.SNOAND SSEX='女'group by SNAMEhaving count(Cno)>=1;

--1.3检索王同学不学的课程的课程号;

SELECT CnoFROM CourseWHERE Cno != ALL(SELECT Course.CnoFROM Student, SC,CourseWHERE (Sname LIKE '王%' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno));

--1.4检索至少选修两门课程的学生学号;

select SNOfrom SCgroup by SNOhaving count(Cno)>=2;

--1.5检索全部学生都选修的课程的课程号与课程名;

SELECT SC.CNO, CNAMEFROM COURSE left join SCon COURSE.CNO=SC.CNOGROUP BY SC.CNO, CNAMEHAVING COUNT()=(SELECT COUNT()FROM STUDENT);

--1.6检索选修了所有3学分课程的学生学号。

SELECT SNOFROM SCWHERE CNO IN (SELECT CNOFROM COURSEWHERE CCREDIT=3)GROUP BY SNOHAVING COUNT()=(SELECT COUNT()FROM COURSEWHERE CCREDIT=3);

--2.1统计所有学生选修的课程门数;

SELECT COUNT(DISTINCT CNO)'所有学生选修课的课程门数'FROM SC;

--2.2求选修4号课程的学生的平均年龄;

SELECT AVG(SAGE)'选修4号课程的学生的平均年龄'FROM SC,STUDENTWHERE STUDENT.SNO=SC.SNOAND CNO=4;

--2.3求学分为3的每门课程的学生平均成绩;

SELECT AVG(GRADE) '学分为3的每门课程的学生的平均成绩'FROM SC, COURSEWHERE SC.Cno = Course.Cno AND Course.Ccredit = 3;

--2.4统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列;

SELECT CNO, COUNT(SNO) NumFROM SCGROUP BY CNOHAVING COUNT(SNO) > 3ORDER BY COUNT(SNO) DESC, CNO ASC;

--2.5检索学号比王非同学大,而年龄比他小的学生姓名;

SELECT SNAMEFROM STUDENTWHERE Sno > (SELECT Sno FROM Student WHERE Sname = '王非')AND Sage < (SELECT Sage FROM Student WHERE Sname = '王非');

--2.6检索姓名以王打头的所有学生的姓名和年龄;

SELECT SNAME,SAGEFROM STUDENTWHERE SNAME LIKE '王%';

--2.7在SC中检索成绩为空值的学生学号和课程号;

SELECT SNO,CNOFROM SCWHERE GRADE IS NULL;

--2.8求年龄大于女同学平均年龄的男学生的姓名和年龄;

SELECT SNAME, SAGEFROM StudentWHERE Ssex = '男' AND Sage > (SELECT AVG(Sage)FROM StudentWHERE Ssex = '女');

--2.9求年龄大于所有女同学年龄的男学生的姓名和年龄;

SELECT SNAME, SAGEFROM StudentWHERE Ssex = '男' AND Sage > ALL(SELECT SageFROM StudentWHERE Ssex = '女');

--2.10检索所有比“王华”年龄大的学生姓名、年龄和性别;SELECT SNAME, SAGE, SSEXFROM STUDENTWHERE Sage >(SELECT SageFROM StudentWHERE Sname = '王华');

--2.11检索选修2号课程的学生中成绩最高的学生的学号;

SELECT SNOFROM SCWHERE CNO=2AND GRADE=(SELECT MAX(GRADE)FROM SCWHERE CNO=2);

--2.12检索学生姓名及其所选修课程的课程号和成绩;

SELECT SNAME,CNO,GRADEFROM STUDENT,SCWHERE STUDENT.SNO=SC.SNO;

--2.13检索选修4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。

SELECT SUM(GRADE) '总成绩'FROM SCWHERE GRADE>=60GROUP BY SNOHAVING COUNT(CNO)>=4ORDER BY '总成绩' DESC;

--3.1查询选修课程“8105”且成绩在80到90之间的所有记录;

SELECT *FROM SCWHERE CNO='8105'AND GRADE between 80 and 90;

--3.2查询成绩为79,89或99的记录;

SELECT *FROM SCWHERE GRADE IN(79,89,99);

--3.3查询9803班的学生人数;

SELECT COUNT(*) '人数'FROM STUDENTWHERE CLASS='9803'

--3.4查询至少有20名学生选修的并以8开头的课程的平均成绩:

SELECT CNO,AVG(GRADE)AS 平均成绩FROM SCWHERE CNO LIKE '8%'GROUP BY CNOHAVING COUNT( DISTINCT SNO)>=20;

--3.5查询最低分大于80,最高分小于95的SNO与平均分;select SNO,avg(GRADE) 平均成绩from SCgroup by SNOhaving min(SC.GRADE) > 80 and max(SC.GRADE) (select gradefrom scwhere sno='9809');

--3.8查询与学号为“9808”的同学同岁的所有学生的SNO、SNAME和AGE;

select SNO,SNAME,AGEFROM STUDENTWHERE AGE IN(SELECT AGEFROM STUDENTWHERE STUDENT.SNO='9809');

--3.9查询“钱军”教师任课的课程号,选修其课程学生的学号和成绩;select SC.*from Course,TEACHER,SCWHERE Teacher.TNO = Course.TNOAND SC.CNO = Course.CNOAND Teacher.TNAME = '钱军'

--3.10查询选修某课程的学生人数多于20人的教师姓名;

select distinct Teacher.TNAMEfrom Teacher,COURSE,SCWHERE Course.TNO = Teacher.TNOAND Course.CNO = SC.CNOAND SC.CNO in(select SC.CNOfrom SCgroup by SC.CNOhaving count(*) > 20);

--3.11查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245"课程的同学的SNO及“8105”课程成绩，并按成绩从高到低次序排列;

select SC.SNO,SC.GRADEfrom SCwhere SC.CNO = '8105'and SC.GRADE > (select GRADEfrom SC as S2where S2.SNO = SC.SNOand S2.CNO = '8245')order by SC.GRADE desc;

--3.12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE;

select CNO,SC.SNO,GRADEfrom SCinner join Studenton Student.SNO = SC.SNOwhere SC.CNO = '8105'and SC.GRADE > (select max(SC.GRADE)from SCwhere SC.CNO = '8245');

--3.13列出所有教师和同学的姓名、SEX、AGE;

select Student.SNAME as 姓名,Student.SEX as 性别,Student.AGE as 年龄from Studentunionselect Teacher.TNAME as 姓名,Teacher.SEX as 性别,Teacher.AGE as 年龄from Teacher;

--3.14查询成绩比该课程平均成绩高的学生的成绩表;

select *from sc awhere a.grade not between 0 and (select avg(grade)from sc bwhere a.cno=b.cno);

--3.15列出所有任课教师的TNAME和DEPT;

select distinct Teacher.TNAME,Teacher.DEPTfrom Teacher,Coursewhere Course.TNO = Teacher.TNOand Course.TNO in (select distinct Course.TNOfrom Course);

--3.16列出所有未讲课教师的TNAME和DEPT;

select TNAME,DEPTfrom Teacherwhere Teacher.TNO not in (select Course.TNOfrom Course);

--3.17列出至少有4名男生的班号;

select Student.CLASSfrom Studentwhere SEX = '男'group by Student.CLASShaving count(*) >= 4;

--3.18查询不姓“张"的学生的记录;

select *from Studentwhere SNAME not like '张%'

--3.19查询每门课最高分的学生的SNO、CNO、GRADE;

select SNO,s1.CNO,GRADEfrom SC as s1where s1.GRADE IN (select max(s2.GRADE)from SC as s2where s1.CNO = s2.CNO);

--3.20查询与“李华”同性别并同班的同学的SNAME;

select SNAMEfrom Studentwhere Student.SEX = (select Student.SEXfrom Studentwhere Student.SNAME = '李华')and Student.CLASS = (select Student.CLASSfrom Studentwhere Student.SNAME = '李华');

--3.21查询“女”教师及其所上的课程;

select Teacher.TNAME,Course.CNO,Course.CNAMEfrom Teacherinner join Courseon Course.TNO = Teacher.TNOwhere Teacher.SEX = '女';

--3.22查询选修“数据库系统”课程的“男”同学的成绩表;

select distinct SC.SNO,Student.SNAME,Student.SEX,Course.CNAME,SC.GRADEfrom SCinner join Student on Student.SNO = SC.SNOinner join Course on Course.CNO = SC.CNOwhere Student.SEX = '男'and Course.CNAME = '数据库系统';

--3.23查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄;

select Teacher.TNAME,AGE as 年龄from Teacherwhere AGE > (select AGEfrom Teacherwhere Teacher.TNAME = '刘涛')or Teacher.TNAME = '刘涛';

--3.24查询不讲授“8104”号课程的教师姓名。

select TNAMEfrom Teacherwhere TNAME not in(select TNAMEfrom Teacherjoin Courseon Course.TNO = Teacher.TNOwhere Course.CNO = '8104');

四、实验小结今天，我们进行了数据库的SQL语言-SELECT查询操作实验，主要学习了如何使用SELECT语句从数据库中查询数据。在实验过程中，我们实践了简单的SELECT查询、带有WHERE子句的查询以及使用AND和OR运算符的组合查询。

实验中，我们首先创建了一个简单的数据库表，并向其中插入了一些数据。接着，使用SELECT语句来查询表中的数据。刚开始，我们执行了一些简单的查询操作，例如查询所有列或特定列的数据。之后，我们学习了如何使用WHERE子句来筛选数据，包括使用比较运算符（如=、>、